//*Set Working Directory if needed*//
//* Should not need to be done if you are running the files   *//
//* per the instructions in the readme file  *//
/* cd Z:\Econ428_Sp15\Metelski\Documentation\Data\ */


//*******************************************************************//
//************************Employment Dataset*************************//
//*******************************************************************//

//*******************************************************************//
//***************************Import Dataset**************************//
//*******************************************************************//
use Merge\finalmergedata.dta

//*******************************************************************//
//*********************Creating State Variable***********************//
//*******************************************************************//

generate state = 0 
replace state = 1 if (area_fips >= 21000 & area_fips <= 21239) 
replace state = 2 if (area_fips >= 39000 & area_fips <= 39175) 
replace state = 3 if (area_fips >= 42000 & area_fips <= 42133) 
replace state = 4 if (area_fips >= 54000 & area_fips <= 54109) 

//*******************************************************************//
//*********************Creating Quarter Dummies**********************//
//*******************************************************************//

egen time2 = concat(year qtr) 
destring time2, replace 
tab time2, generate (quarter) 

generate quarter = 0 
replace quarter = 1 if quarter1 == 1 
replace quarter = 2 if quarter2 == 1 
replace quarter = 3 if quarter3 == 1 
replace quarter = 4 if quarter4 == 1 
replace quarter = 5 if quarter5 == 1 
replace quarter = 6 if quarter6 == 1 
replace quarter = 7 if quarter7 == 1 
replace quarter = 8 if quarter8 == 1 
replace quarter = 9 if quarter9 == 1 
replace quarter = 10 if quarter10 == 1 
replace quarter = 11 if quarter11 == 1 
replace quarter = 12 if quarter12 == 1 
replace quarter = 13 if quarter13 == 1 
replace quarter = 14 if quarter14 == 1 
replace quarter = 15 if quarter15 == 1 
replace quarter = 16 if quarter16 == 1 
replace quarter = 17 if quarter17 == 1 
replace quarter = 18 if quarter18 == 1 
replace quarter = 19 if quarter19 == 1 
replace quarter = 20 if quarter20 == 1 
replace quarter = 21 if quarter21 == 1 
replace quarter = 22 if quarter22 == 1 
replace quarter = 23 if quarter23 == 1 
replace quarter = 24 if quarter24 == 1 

//*******************************************************************//
//************************Identifying Panel/Time*********************//
//*******************************************************************//

generate time = tq(2006q1) + quarter - 1 
xtset area_fips time, quarterly 

//*******************************************************************//
//**********************Dropping Missing Values**********************//
//*******************************************************************//

drop if state == 2 
drop if state == 3 

drop if area_fips == 21195
drop if area_fips == 54005

replace allindustriesdisclosurecode	 = subinstr(allindustriesdisclosurecode, "N","1",.) 
replace allindustriesdisclosurecode = subinstr(allindustriesdisclosurecode, "","0",.) 
destring allindustriesdisclosurecode, replace force 
drop if allindustriesdisclosurecode == 1 

drop if constructionemploymentmo2 == . 
drop if manufacturingemploymentmo2 == . 
drop if serviceemploymentmo2 == . 
drop if retailtradeemploymentmo2 == . 

drop if disclosure_code == 1 

recode minecount (missing = 0)  
drop if (minecount >= 1 & coalemploymentmo2 == .)

//*******************************************************************//
//**********Generate Var for % of Total Earnings from Coal***********//
//*******************************************************************//

generate percentcoal = coaltotalwages/allindustriestotalwages
recode percentcoal (missing = 0) 


//*******************************************************************//
//*********************Creating Treatment Group**********************//
//*******************************************************************//

generate treatment = 0 
replace treatment = 1 if percentcoal >=.05

replace treatment = 1 if area_fips ==21013
replace treatment = 1 if area_fips ==21051
replace treatment = 1 if area_fips ==21095
replace treatment = 1 if area_fips ==21115
replace treatment = 1 if area_fips ==21119
replace treatment = 1 if area_fips ==21133
replace treatment = 1 if area_fips ==21177
replace treatment = 1 if area_fips ==21193
replace treatment = 1 if area_fips ==21225
replace treatment = 1 if area_fips ==39013
replace treatment = 1 if area_fips ==39067
replace treatment = 1 if area_fips ==42005
replace treatment = 1 if area_fips ==42059
replace treatment = 1 if area_fips ==54001
replace treatment = 1 if area_fips ==54019
replace treatment = 1 if area_fips ==54023
replace treatment = 1 if area_fips ==54025
replace treatment = 1 if area_fips ==54045
replace treatment = 1 if area_fips ==54047
replace treatment = 1 if area_fips ==54059
replace treatment = 1 if area_fips ==54067
replace treatment = 1 if area_fips ==54077
replace treatment = 1 if area_fips ==54081
replace treatment = 1 if area_fips ==54097
replace treatment = 1 if area_fips ==54109

//*******************************************************************//
//*********************Putting Zero in for MV************************//
//*******************************************************************//

mvencode coalemploymentmo1 , mv(0) 
mvencode coalemploymentmo2 , mv(0) 
mvencode coalemploymentmo3 , mv(0) 
mvencode coaltotalwages , mv(0) 
mvencode coaltaxablewages , mv(0) 
mvencode coalqrtcontribution , mv(0) 
mvencode coalavgwklywage , mv(0) 

//*******************************************************************//
//*************Generate Var for All Industries Minus Coal************//
//*******************************************************************//

generate allindustrymcoalemployment = allindustriesemploymentmo3 - coalemploymentmo3
generate allindustrymcoaltotalwage = allindustriestotalwages - coaltotalwages 

//*******************************************************************//
//*********************Creating Comparison Group*********************//
//*******************************************************************//

generate comparison = 0 
replace comparison = 1 if (percentcoal == 0 & Totalpopestimate2006 <= 80000 & Totalpopestimate2006 >= 10000) 
replace comparison = 0 if minecount >= 1

//*******************************************************************//
//********************Creating Medium Coal Group*********************//
//*******************************************************************//

generate mediumcoal = 0 
replace mediumcoal = 1 if (minecount >= 10 & Totalpopestimate2006 <= 80000 & Totalpopestimate2006 >= 10000)
replace mediumcoal = 0 if treatment == 1
replace mediumcoal = 0 if comparison == 1 

//*******************************************************************//
//*********************Creating Low Coal Group***********************//
//*******************************************************************//

generate lowcoal = 0 
replace lowcoal = 1 if (minecount < 10 & minecount > 0 & Totalpopestimate2006 <= 80000 & Totalpopestimate2006 >= 10000)
replace lowcoal = 0 if treatment == 1
replace lowcoal = 0 if comparison == 1 

//*******************************************************************//
//*********************Creating Less Coal Group**********************//
//*******************************************************************//

generate lesscoal = 0 
replace lesscoal = 1 if (percentcoal <= .05 & percentcoal != 0 & Totalpopestimate2006 <= 80000 & Totalpopestimate2006 >= 10000)
replace lesscoal = 0 if treatment == 1
replace lesscoal = 0 if comparison == 1 

//*******************************************************************//
//***********************Creating Coal Group*************************//
//*******************************************************************//

generate coal = 0 
replace coal = 1 if coalemploymentmo3 != 0  
replace coal = 0 if area_fips == 21000
replace coal = 0 if area_fips == 39000
replace coal = 0 if area_fips == 42000
replace coal = 0 if area_fips == 54000


//*******************************************************************//
//*********************Creating 4 Time Periods***********************//
//*******************************************************************//

generate pre = 0 
replace pre = 1 if quarter <= 8 

generate boom = 0 
replace boom = 1 if (quarter >= 9 & quarter <= 12)

generate bust = 0 
replace bust = 1 if (quarter >=13 & quarter <= 17)

generate post = 0 
replace post = 1 if quarter >=18

//*******************************************************************//
//**********************Creating Year Dummies************************//
//*******************************************************************//

generate yr2006 = 0 
replace yr2006 = 1 if year == 2006

generate yr2007 = 0 
replace yr2007 = 1 if year == 2007 

generate yr2008 = 0 
replace yr2008 = 1 if year == 2008 

generate yr2009 = 0 
replace yr2009 = 1 if year == 2009

generate yr2010 = 0 
replace yr2010 = 1 if year == 2010

generate yr2011 = 0 
replace yr2011 = 1 if year == 2011

//*******************************************************************//
//********************Creating Seasonal Dummies**********************//
//*******************************************************************//

generate Q1 = 0 
replace Q1 = 1 if qtr == 1 

generate Q2 = 0 
replace Q2 = 1 if qtr == 2 

generate Q3 = 0 
replace Q3 = 1 if qtr == 3 

generate Q4 = 0 
replace Q4 = 1 if qtr == 4 

//*******************************************************************//
//**********************Creating State Dummies***********************//
//*******************************************************************//

generate KY = 0 
replace KY = 1 if state == 1

generate OH = 0 
replace OH = 1 if state == 2 

generate PA = 0 
replace PA = 1 if state == 3 

generate WV = 0 
replace WV = 1 if state == 4

//*******************************************************************//
//*******************Creating Interaction Terms**********************//
//*******************************************************************//

generate treatmentpre = treatment*pre 
generate treatmentboom = treatment*boom 
generate treatmentbust = treatment*bust
generate treatmentpost = treatment*post

generate comparisonpre = comparison*pre 
generate comparisonboom = comparison*boom 
generate comparisonbust = comparison*bust
generate comparisonpost = comparison*post

generate mediumpre = mediumcoal*pre 
generate mediumboom = mediumcoal*boom 
generate mediumbust = mediumcoal*bust
generate mediumpost = mediumcoal*post

generate lowpre = lowcoal*pre 
generate lowboom = lowcoal*boom 
generate lowbust = lowcoal*bust
generate lowpost = lowcoal*post

generate coalpre = coal*pre 
generate coalboom = coal*boom 
generate coalbust = coal*bust
generate coalpost = coal*post 

generate lesspre = lesscoal*pre
generate lessboom = lesscoal*boom
generate lessbust = lesscoal*bust
generate lesspost = lesscoal*post

generate KY2006 = 0 
	replace KY2006 = 1 if (state == 1 & year == 2006)
generate KY2007 = 0 
	replace KY2007 = 1 if (state == 1 & year == 2007)
generate KY2008 = 0 
	replace KY2008 = 1 if (state == 1 & year == 2008)
generate KY2009 = 0 
	replace KY2009 = 1 if (state == 1 & year == 2009)
generate KY2010 = 0
	replace KY2010 = 1 if (state == 1 & year == 2010)
generate KY2011 = 0
	replace KY2011 = 1 if (state == 1 & year == 2011)

generate OH2006 = 0 	
	replace OH2006 = 1 if (state == 2 & year == 2006)
generate OH2007 = 0 	
	replace OH2007 = 1 if (state == 2 & year == 2007)
generate OH2008 = 0 
	replace OH2008 = 1 if (state == 2 & year == 2008)
generate OH2009 = 0 
	replace OH2009 = 1 if (state == 2 & year == 2009)
generate OH2010 = 0 
	replace OH2010 = 1 if (state == 2 & year == 2010)

generate PA2006 = 0 
	replace PA2006 = 1 if (state == 3 & year == 2006)
generate PA2007 = 0 
	replace PA2007 = 1 if (state == 3 & year == 2007)
generate PA2008 = 0 
	replace PA2008 = 1 if (state == 3 & year == 2008)
generate PA2009 = 0 
	replace PA2009 = 1 if (state == 3 & year == 2009)
generate PA2010 = 0 
	replace PA2010 = 1 if (state == 3 & year == 2010)

generate WV2006 = 0 	
	replace WV2006 = 1 if (state == 4 & year == 2006)
generate WV2007 = 0 
	replace WV2007 = 1 if (state == 4 & year == 2007)
generate WV2008 = 0 
	replace WV2008 = 1 if (state == 4 & year == 2008)
generate WV2009 = 0 
	replace WV2009 = 1 if (state == 4 & year == 2009)
generate WV2010 = 0 
	replace WV2010 = 1 if (state == 4 & year == 2010)
generate WV2011 = 0 
	replace WV2011 = 1 if (state == 4 & year == 2011)


//*******************************************************************//
//**********************Creating Logged Variables********************//
//*******************************************************************//

generate lncoalemployment = ln(coalemploymentmo3)
generate lncoaltotalwages = ln(coaltotalwages)
generate lncoalweeklywage = ln(coalavgwklywage)

generate lnconstructionemployment = ln(constructionemploymentmo3)
generate lnconstructiontotalwages = ln(constructiontotalwages)
generate lnconstructionweeklywage = ln(constructionavgwklywage)

generate lnmanufacturingemployment = ln(manufacturingemploymentmo3)
generate lnmanufacturingtotalwages = ln(manufacturingtotalwages)
generate lnmanufacturingweeklywage = ln(manufacturingavgwklywage)

generate lnretailemployment = ln(retailtradeemploymentmo3)
generate lnretailtotalwages = ln(retailtradetotalwages)
generate lnretailweeklywage = ln(retailtradeavgwklywage)

generate lnserviceemployment = ln(serviceemploymentmo3)
generate lnservicetotalwages = ln(servicetotalwages)
generate lnserviceweeklywage = ln(serviceavgwklywage)

generate lnallindustriesemployment = ln(allindustriesemploymentmo3)
generate lnallindustriestotalwages = ln(allindustriestotalwages)
generate lnallindustriesweeklywage = ln(allindustriesavgwklywage)

generate lnallindustrymcoalemployment = ln(allindustrymcoalemployment)
generate lnallindustrymcoaltotalwages = ln(allindustrymcoaltotalwage)

generate femalepop100 = Femalepop/100
generate malepop100 = Malepop/100 
generate unemployed100 = unemployed/100  


//*******************************************************************// 
//***************Drop Neighboring Comparison Counties****************//
//*******************************************************************//
drop if area_fips == 54031
drop if area_fips == 54091
drop if area_fips == 54041
drop if area_fips == 54007
drop if area_fips == 54089
drop if area_fips == 54063 
drop if area_fips == 21031
drop if area_fips == 21141
drop if area_fips == 21219

//*******************************************************************//
//*****************Drop Low Coal Producing Counties******************//
//*******************************************************************//
drop if lesscoal == 1
drop if mediumcoal == 1 
drop if lowcoal == 1 

//*******************************************************************//
//*****Drop Treatment Counties with Material Missing Observations****//
//*******************************************************************//

drop if area_fips == 54023 
drop if area_fips == 21175

//*******************************************************************//
//*********************Drop Unneeded Observations********************//
//*******************************************************************//
//*To Prevent KY State Data from Being Dropped for Data Appendix*//
replace treatment = 1 if area_fips == 21000 
drop if (treatment == 0 & comparison == 0 & lowcoal == 0 & mediumcoal == 0 & lesscoal == 0)

//*******************************************************************//
//****************Save Cleaned Dataset for Data Appendix*************//
//*******************************************************************//

save Final\finaldatacleanappendix.dta , replace 

//*******************************************************************//
//****************Drop State-Level Employment Data*******************//
//*******************************************************************//

drop if area_fips == 54000
drop if area_fips == 21000

//*******************************************************************//
//***********************Drop Unneeded Variables*********************//
//*******************************************************************//

keep lncoalemployment lncoalweeklywage lncoaltotalwage lnallindustriesemployment lnallindustriesweeklywage lnallindustriestotalwage lnconstructionemployment lnconstructiontotalwages lnconstructionweeklywage lnmanufacturingemployment lnmanufacturingtotalwages lnmanufacturingweeklywage lnretailemployment lnretailtotalwages lnretailweeklywage lnserviceemployment lnservicetotalwages lnserviceweeklywage lnallindustriesemployment  lnallindustriestotalwages lnallindustriesweeklywage pre boom bust post treatmentpre treatmentboom treatmentbust treatmentpost Q1 Q2 Q3 Q4 malepop100 femalepop100 unemployed100 time area_fips area_title treatment comparison year qtr coalemploymentmo3 lnallindustrymcoalemployment lnallindustrymcoaltotalwages 


//*******************************************************************//
//************************Save Cleaned Dataset***********************//
//*******************************************************************//

sort area_fips year qtr
save Final\finaldataclean.dta , replace 
clear 

